const express = require('express');
const cors = require('cors');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const path = require('path');
const axios = require('axios');
const app = express();
const PORT = process.env.PORT || 3001;
const JWT_SECRET = 'daydayup-secret-key-2025';

// 中间件
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// 生成北京时间的工具函数
const getBeijingTime = () => {
  const now = new Date();
  // 北京时间 = UTC时间 + 8小时
  const beijingTime = new Date(now.getTime() + 8 * 60 * 60 * 1000);
  // 格式化为 'YYYY-MM-DD HH:MM:SS'
  return beijingTime.toISOString().slice(0, 19).replace('T', ' ');
};

// 数据库初始化
const db = new sqlite3.Database('./database.db', (err) => {
  if (err) {
    console.error('数据库连接错误:', err.message);
  } else {
    console.log('已连接到SQLite数据库');
    initDatabase();
  }
});

// 初始化数据库表
function initDatabase() {
  // 使用serialize()确保语句按顺序执行，前一个完成后再执行下一个
  db.serialize(() => {
    // 1. 创建用户表
    db.run(`CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      is_admin INTEGER DEFAULT 0,
      created_at DATETIME
    )`, (err) => {
      if (err) console.error('创建users表失败:', err.message);
    });

    // 2. 创建邀请码表（关键：确保此表创建完成后再执行后续查询）
    db.run(`CREATE TABLE IF NOT EXISTS invite_codes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      code TEXT UNIQUE NOT NULL,
      created_by INTEGER NOT NULL,
      used_by INTEGER,
      is_used INTEGER DEFAULT 0,
      created_at DATETIME,
      used_at DATETIME,
      FOREIGN KEY (created_by) REFERENCES users (id),
      FOREIGN KEY (used_by) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建invite_codes表失败:', err.message);
    });

    // 3. 创建全局邀请开关表
    db.run(`CREATE TABLE IF NOT EXISTS global_invite_settings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      allow_all_users_invite INTEGER DEFAULT 0,
      updated_by INTEGER,
      updated_at DATETIME,
      FOREIGN KEY (updated_by) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建global_invite_settings表失败:', err.message);
    });

    // 4. 创建日志表
    db.run(`CREATE TABLE IF NOT EXISTS diaries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      date TEXT NOT NULL,
      content TEXT,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建diaries表失败:', err.message);
    });

    // 5. 创建内心历程表
    db.run(`CREATE TABLE IF NOT EXISTS inner_thoughts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      date TEXT NOT NULL,
      content TEXT,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建inner_thoughts表失败:', err.message);
    });

    // 6. 创建计时记录表
    db.run(`CREATE TABLE IF NOT EXISTS timer_records (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      date TEXT NOT NULL,
      duration INTEGER NOT NULL,
      type TEXT NOT NULL,
      created_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建timer_records表失败:', err.message);
    });

    // 7. 创建笔记分组表
    db.run(`CREATE TABLE IF NOT EXISTS note_groups (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      name TEXT NOT NULL,
      created_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建note_groups表失败:', err.message);
    });

    // 8. 创建笔记表
    db.run(`CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      group_id INTEGER,
      content TEXT NOT NULL,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id),
      FOREIGN KEY (group_id) REFERENCES note_groups (id)
    )`, (err) => {
      if (err) console.error('创建notes表失败:', err.message);
    });

    // 9. 创建工作预设表
    db.run(`CREATE TABLE IF NOT EXISTS work_presets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      morning_start TEXT NOT NULL,
      morning_end TEXT NOT NULL,
      afternoon_start TEXT NOT NULL,
      afternoon_end TEXT NOT NULL,
      evening_start TEXT NOT NULL,
      evening_end TEXT NOT NULL,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建work_presets表失败:', err.message);
    });

    // 10. 创建倒数日分类表
    db.run(`CREATE TABLE IF NOT EXISTS countdown_categories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )`, (err) => {
      if (err) console.error('创建countdown_categories表失败:', err.message);
    });

    // 11. 创建倒数日事件表
    db.run(`CREATE TABLE IF NOT EXISTS countdown_events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      category_id INTEGER NOT NULL,
      title TEXT NOT NULL,
      target_date TEXT NOT NULL,
      is_pinned INTEGER DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at DATETIME,
      updated_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users (id),
      FOREIGN KEY (category_id) REFERENCES countdown_categories (id)
    )`, (err) => {
      if (err) console.error('创建countdown_events表失败:', err.message);
    });

    // 12. 所有表创建完成后，检查用户数量和邀请码数量
    db.get('SELECT COUNT(*) as count FROM users', (err, userResult) => {
      if (err) {
        console.error('检查用户数量失败:', err);
        return;
      }
      
      if (userResult.count === 0) {
        // 检查邀请码是否已存在
        db.get('SELECT COUNT(*) as count FROM invite_codes WHERE code = ?', ['DaydayupADMIN2025'], (err, inviteResult) => {
          if (err) {
            console.error('检查邀请码失败:', err);
            return;
          }
          
          if (inviteResult.count === 0) {
            // 创建第一个用户（管理员）的邀请码
            const initialCode = 'DaydayupADMIN2025';
            const createdAt = getBeijingTime();
            
            db.run('INSERT INTO invite_codes (code, created_by, created_at) VALUES (?, ?, ?)', 
              [initialCode, 1, createdAt], (err) => {
                if (err) {
                  console.error('创建初始邀请码失败:', err);
                } else {
                  console.log('已创建初始邀请码:', initialCode);
                }
              });
          } else {
            console.log('初始邀请码已存在，跳过创建');
          }
        });
      }
    });

    // 13. 初始化全局邀请设置
    db.get('SELECT COUNT(*) as count FROM global_invite_settings', (err, settingResult) => {
      if (err) {
        console.error('检查全局邀请设置失败:', err);
        return;
      }
      
      if (settingResult.count === 0) {
        const createdAt = getBeijingTime();
        db.run('INSERT INTO global_invite_settings (allow_all_users_invite, updated_at) VALUES (?, ?)', 
          [0, createdAt], (err) => {
            if (err) {
              console.error('初始化全局邀请设置失败:', err);
            } else {
              console.log('已初始化全局邀请设置');
            }
          });
      }
    });

    console.log('数据库表初始化完成（含邀请码功能和管理员权限和倒数日模块）');
    
    // 为现有用户插入默认分类
    insertDefaultCategories();
  });
}

// 为所有用户插入默认分类
function insertDefaultCategories() {
  db.all('SELECT id FROM users', [], (err, users) => {
    if (err) {
      console.error('获取用户列表失败:', err);
      return;
    }
    
    users.forEach(user => {
      const currentTime = getBeijingTime();
      
      // 检查是否已存在默认分类
      db.get('SELECT id FROM countdown_categories WHERE user_id = ? AND name = ?', 
        [user.id, '未分组'], (err, existing) => {
          if (err) {
            console.error('检查默认分类失败:', err);
            return;
          }
          
          if (!existing) {
            // 插入默认分类
            db.run('INSERT INTO countdown_categories (user_id, name, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', 
              [user.id, '未分组', 1, currentTime, currentTime], function(err) {
                if (err) {
                  console.error('插入默认分类失败:', err);
                } else {
                  console.log(`为用户 ${user.id} 插入默认分类成功`);
                }
              });
          }
        });
    });
  });
}

// JWT验证中间件
function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: '访问令牌缺失' });
  }

  jwt.verify(token, JWT_SECRET, (err, user) => {
    if (err) {
      return res.status(403).json({ error: '无效的访问令牌' });
    }
    req.user = user;
    next();
  });
}

// 用户注册
app.post('/api/register', async (req, res) => {
  try {
    const { username, password, inviteCode } = req.body;
    const createdAt = getBeijingTime(); // 使用北京时间
    
    if (!username || !password) {
      return res.status(400).json({ error: '用户名和密码不能为空' });
    }

    if (!inviteCode) {
      return res.status(400).json({ error: '邀请码不能为空' });
    }

    // 验证邀请码
    db.get('SELECT * FROM invite_codes WHERE code = ? AND is_used = 0', [inviteCode], async (err, inviteCodeData) => {
      if (err) {
        return res.status(500).json({ error: '验证邀请码失败' });
      }

      if (!inviteCodeData) {
        return res.status(400).json({ error: '邀请码无效或已被使用' });
      }

      const hashedPassword = await bcrypt.hash(password, 10);
      
      // 开始事务
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 检查是否是第一个用户
        db.get('SELECT COUNT(*) as count FROM users', (err, userCountResult) => {
          if (err) {
            db.run('ROLLBACK');
            return res.status(500).json({ error: '注册失败' });
          }
          
          const isFirstUser = userCountResult.count === 0;
          const isAdmin = isFirstUser ? 1 : 0;
          
          // 创建用户
              db.run('INSERT INTO users (username, password, is_admin, created_at) VALUES (?, ?, ?, ?)', 
                [username, hashedPassword, isAdmin, createdAt], 
                function(err) {
                  if (err) {
                    db.run('ROLLBACK');
                    if (err.message.includes('UNIQUE constraint failed')) {
                      return res.status(500).json({ error: '用户名已存在' });
                    }
                    return res.status(500).json({ error: '注册失败' });
                  }
                  
                  const userId = this.lastID;
                  
                  // 为新用户创建默认分类
                  db.run('INSERT INTO countdown_categories (user_id, name, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', 
                    [userId, '未分组', 1, createdAt, createdAt], 
                    function(err) {
                      if (err) {
                        db.run('ROLLBACK');
                        return res.status(500).json({ error: '注册失败' });
                      }
                      
                      // 标记邀请码为已使用
                      db.run('UPDATE invite_codes SET is_used = 1, used_by = ?, used_at = ? WHERE code = ?', 
                        [userId, createdAt, inviteCode], 
                        function(err) {
                          if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: '注册失败' });
                          }
                          
                          db.run('COMMIT');
                          res.json({ 
                            message: '注册成功', 
                            userId: userId,
                            isAdmin: isAdmin
                          });
                        });
                    });
                });
          });
        });
      });
    // });
  } catch (error) {
    res.status(500).json({ error: '服务器错误' });
  }
});

// 用户登录
app.post('/api/login', (req, res) => {
  const { username, password } = req.body;
  
  if (!username || !password) {
    return res.status(400).json({ error: '用户名和密码不能为空' });
  }

  db.get('SELECT * FROM users WHERE username = ?', [username], async (err, user) => {
    if (err) {
      return res.status(500).json({ error: '登录失败' });
    }
    
    if (!user) {
      return res.status(401).json({ error: '用户名或密码错误' });
    }

    const validPassword = await bcrypt.compare(password, user.password);
    if (!validPassword) {
      return res.status(401).json({ error: '用户名或密码错误' });
    }
    //token 一年有效
    const token = jwt.sign({ userId: user.id, username: user.username, isAdmin: user.is_admin === 1 }, JWT_SECRET, { expiresIn: '1y' });
    res.json({ 
      message: '登录成功', 
      token, 
      user: { id: user.id, username: user.username, isAdmin: user.is_admin === 1 } 
    });
  });
});

// 获取日志内容
app.get('/api/diary/:date', authenticateToken, (req, res) => {
  const { date } = req.params;
  const userId = req.user.userId;

  db.get('SELECT * FROM diaries WHERE user_id = ? AND date = ?', 
    [userId, date], (err, diary) => {
      if (err) {
        return res.status(500).json({ error: '获取日志失败' });
      }
      res.json({ diary: diary || { content: '', updated_at: null } });
    });
});

// 保存日志内容
app.post('/api/diary/:date', authenticateToken, (req, res) => {
  const { date } = req.params;
  const { content } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime(); // 使用北京时间

  db.get('SELECT id FROM diaries WHERE user_id = ? AND date = ?', [userId, date], (err, existing) => {
    if (err) return res.status(500).json({ error: '操作失败' });

    const sql = existing 
      ? 'UPDATE diaries SET content = ?, updated_at = ? WHERE id = ?'
      : 'INSERT INTO diaries (user_id, date, content, created_at, updated_at) VALUES (?, ?, ?, ?, ?)';
      
    const params = existing 
      ? [content, currentTime, existing.id] 
      : [userId, date, content, currentTime, currentTime];

    db.run(sql, params, function(err) {
      if (err) return res.status(500).json({ error: existing ? '更新失败' : '创建失败' });
      res.json({
        message: existing ? '日志更新成功' : '日志创建成功',
        action: existing ? 'update' : 'insert',
        ...(existing ? {} : { diaryId: this.lastID })
      });
    });
  });
});

// 获取内心历程内容
app.get('/api/inner/:date', authenticateToken, (req, res) => {
  const { date } = req.params;
  const userId = req.user.userId;

  db.get('SELECT * FROM inner_thoughts WHERE user_id = ? AND date = ?', 
    [userId, date], (err, inner) => {
      if (err) {
        return res.status(500).json({ error: '获取内心历程失败' });
      }
      res.json({ inner: inner || { content: '', updated_at: null } });
    });
});

// 保存内心历程内容
app.post('/api/inner/:date', authenticateToken, (req, res) => {
  const { date } = req.params;
  const { content } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime(); // 使用北京时间

  db.get('SELECT id FROM inner_thoughts WHERE user_id = ? AND date = ?', [userId, date], (err, existing) => {
    if (err) return res.status(500).json({ error: '操作失败' });

    const sql = existing 
      ? 'UPDATE inner_thoughts SET content = ?, updated_at = ? WHERE id = ?'
      : 'INSERT INTO inner_thoughts (user_id, date, content, created_at, updated_at) VALUES (?, ?, ?, ?, ?)';
      
    const params = existing 
      ? [content, currentTime, existing.id] 
      : [userId, date, content, currentTime, currentTime];

    db.run(sql, params, function(err) {
      if (err) return res.status(500).json({ error: existing ? '更新失败' : '创建失败' });
      res.json({
        message: existing ? '内心历程更新成功' : '内心历程创建成功',
        action: existing ? 'update' : 'insert',
        ...(existing ? {} : { innerId: this.lastID })
      });
    });
  });
});

// 保存计时记录
app.post('/api/timer/record', authenticateToken, (req, res) => {
  const { date, duration, type } = req.body;
  const userId = req.user.userId;
  const createdAt = getBeijingTime(); // 使用北京时间

  db.run('INSERT INTO timer_records (user_id, date, duration, type, created_at) VALUES (?, ?, ?, ?, ?)', 
    [userId, date, duration, type, createdAt], function(err) {
      if (err) {
        return res.status(500).json({ error: '保存计时记录失败' });
      }
      res.json({ message: '记录保存成功' });
    });
});

// 获取指定日期的计时记录
app.get('/api/timer/:date', authenticateToken, (req, res) => {
  const { date } = req.params;
  const userId = req.user.userId;

  db.all('SELECT * FROM timer_records WHERE user_id = ? AND date = ?', 
    [userId, date], (err, records) => {
      if (err) {
        return res.status(500).json({ error: '获取计时记录失败' });
      }
      
      const totalTime = records.reduce((sum, record) => sum + record.duration, 0);
      const countdownTime = records.filter(r => r.type === 'countdown').reduce((sum, r) => sum + r.duration, 0);
      const timerTime = records.filter(r => r.type === 'timer').reduce((sum, r) => sum + r.duration, 0);
      
      res.json({ 
        records, 
        totalTime, 
        countdownTime, 
        timerTime,
        hasRecords: records.length > 0
      });
    });
});

// 获取日历数据
app.get('/api/calendar/:year/:month', authenticateToken, (req, res) => {
  const { year, month } = req.params;
  const userId = req.user.userId;
  const startDate = `${year}-${month.padStart(2, '0')}-01`;
  const endDate = `${year}-${month.padStart(2, '0')}-31`;

  db.all(`SELECT date, SUM(duration) as total_duration, 
           SUM(CASE WHEN type = 'countdown' THEN duration ELSE 0 END) as countdown_duration,
           SUM(CASE WHEN type = 'timer' THEN duration ELSE 0 END) as timer_duration,
           COUNT(*) as record_count
           FROM timer_records 
           WHERE user_id = ? AND date BETWEEN ? AND ?
           GROUP BY date`, 
    [userId, startDate, endDate], (err, timerData) => {
      if (err) {
        return res.status(500).json({ error: '获取日历数据失败' });
      }

      // 获取日志数据
      db.all(`SELECT date FROM diaries WHERE user_id = ? AND date BETWEEN ? AND ?`, 
        [userId, startDate, endDate], (err, diaryData) => {
          if (err) {
            return res.status(500).json({ error: '获取日志数据失败' });
          }

          const calendarData = {};
          
          // 合并计时数据
          timerData.forEach(item => {
            calendarData[item.date] = {
              totalTime: item.total_duration,
              countdownTime: item.countdown_duration,
              timerTime: item.timer_duration,
              hasTimerRecords: true
            };
          });

          // 合并日志数据
          diaryData.forEach(item => {
            if (calendarData[item.date]) {
              calendarData[item.date].hasDiary = true;
            } else {
              calendarData[item.date] = { hasDiary: true };
            }
          });

          res.json({ calendarData });
        });
    });
});

// 获取今日总计时间
app.get('/api/timer/today/total', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  const today = getBeijingTime().split(' ')[0]; // 使用北京时间的日期部分

  db.get(`SELECT SUM(duration) as total_duration FROM timer_records 
           WHERE user_id = ? AND date = ?`, 
    [userId, today], (err, result) => {
      if (err) {
        return res.status(500).json({ error: '获取今日总计失败' });
      }
      res.json({ totalTime: result.total_duration || 0 });
    });
});

// 获取月度计时记录
app.get('/api/timer/monthly/:year/:month', authenticateToken, (req, res) => {
  const { year, month } = req.params;
  const userId = req.user.userId;
  const startDate = `${year}-${month.padStart(2, '0')}-01`;
  const endDate = `${year}-${month.padStart(2, '0')}-31`;

  db.all(`SELECT date, SUM(duration) as total_duration, 
           SUM(CASE WHEN type = 'countdown' THEN duration ELSE 0 END) as countdown_duration,
           SUM(CASE WHEN type = 'timer' THEN duration ELSE 0 END) as timer_duration
           FROM timer_records 
           WHERE user_id = ? AND date BETWEEN ? AND ?
           GROUP BY date
           ORDER BY date`, 
    [userId, startDate, endDate], (err, records) => {
      if (err) {
        return res.status(500).json({ error: '获取月度计时记录失败' });
      }

      if (records.length === 0) {
        return res.json({
          hasRecords: false,
          totalTime: 0,
          countdownTime: 0,
          timerTime: 0,
          recordDays: 0,
          dailyStats: []
        });
      }

      // 计算总计时间
      const totalTime = records.reduce((sum, record) => sum + record.total_duration, 0);
      const countdownTime = records.reduce((sum, record) => sum + record.countdown_duration, 0);
      const timerTime = records.reduce((sum, record) => sum + record.timer_duration, 0);
      const recordDays = records.length;

      // 生成每日统计数据
      const dailyStats = records.map(record => {
        const date = new Date(record.date);
        return {
          date: record.date,
          day: date.getDate(),
          totalTime: record.total_duration,
          countdownTime: record.countdown_duration,
          timerTime: record.timer_duration
        };
      });

      res.json({
        hasRecords: true,
        totalTime,
        countdownTime,
        timerTime,
        recordDays,
        dailyStats
      });
    });
});

// 检查用户是否为管理员
function isAdminUser(userId, callback) {
  db.get('SELECT is_admin FROM users WHERE id = ?', [userId], (err, user) => {
    if (err) {
      callback(err, false);
    } else {
      callback(null, user && user.is_admin === 1);
    }
  });
}

// 获取全局邀请设置（所有用户可访问）
app.get('/api/invite-settings', authenticateToken, (req, res) => {
  db.get('SELECT * FROM global_invite_settings ORDER BY id DESC LIMIT 1', (err, settings) => {
    if (err) {
      return res.status(500).json({ error: '获取设置失败' });
    }
    
    res.json({ settings: settings || { allow_all_users_invite: 0 } });
  });
});

// 获取全局邀请设置（仅管理员可访问，用于管理）
app.get('/api/admin/invite-settings', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  
  isAdminUser(userId, (err, isAdmin) => {
    if (err) {
      return res.status(500).json({ error: '检查权限失败' });
    }
    
    if (!isAdmin) {
      return res.status(403).json({ error: '需要管理员权限' });
    }
    
    db.get('SELECT * FROM global_invite_settings ORDER BY id DESC LIMIT 1', (err, settings) => {
      if (err) {
        return res.status(500).json({ error: '获取设置失败' });
      }
      
      res.json({ settings: settings || { allow_all_users_invite: 0 } });
    });
  });
});

// 更新全局邀请设置
app.put('/api/admin/invite-settings', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  const { allowAllUsersInvite } = req.body;
  
  isAdminUser(userId, (err, isAdmin) => {
    if (err) {
      return res.status(500).json({ error: '检查权限失败' });
    }
    
    if (!isAdmin) {
      return res.status(403).json({ error: '需要管理员权限' });
    }
    
    const currentTime = getBeijingTime();
    const allowValue = allowAllUsersInvite ? 1 : 0;
    
    db.run('UPDATE global_invite_settings SET allow_all_users_invite = ?, updated_by = ?, updated_at = ? WHERE id = (SELECT MAX(id) FROM global_invite_settings)', 
      [allowValue, userId, currentTime], 
      function(err) {
        if (err) {
          return res.status(500).json({ error: '更新设置失败' });
        }
        
        res.json({ 
          message: '设置更新成功',
          allowAllUsersInvite: allowValue === 1
        });
      });
  });
});

// 生成邀请码
app.post('/api/invite/generate', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  const createdAt = getBeijingTime();
  
  // 检查用户是否有权限生成邀请码
  db.get('SELECT u.is_admin, gis.allow_all_users_invite FROM users u LEFT JOIN global_invite_settings gis ON gis.id = (SELECT MAX(id) FROM global_invite_settings) WHERE u.id = ?', [userId], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '检查权限失败' });
    }
    
    const isAdmin = result && result.is_admin === 1;
    const allowAllUsersInvite = result && result.allow_all_users_invite === 1;
    
    if (!isAdmin && !allowAllUsersInvite) {
      return res.status(403).json({ error: '当前不允许生成邀请码，请联系管理员' });
    }
    
    // 生成10位随机邀请码
    const generateInviteCode = () => {
      const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
      let result = '';
      for (let i = 0; i < 10; i++) {
        result += chars.charAt(Math.floor(Math.random() * chars.length));
      }
      return result;
    };

    const inviteCode = generateInviteCode();
    
    db.run('INSERT INTO invite_codes (code, created_by, created_at) VALUES (?, ?, ?)', 
      [inviteCode, userId, createdAt], 
      function(err) {
        if (err) {
          if (err.message.includes('UNIQUE constraint failed')) {
            // 如果邀请码重复，重新生成
            const newInviteCode = generateInviteCode();
            db.run('INSERT INTO invite_codes (code, created_by, created_at) VALUES (?, ?, ?)', 
              [newInviteCode, userId, createdAt], 
              function(err) {
                if (err) {
                  return res.status(500).json({ error: '生成邀请码失败' });
                }
                res.json({ message: '邀请码生成成功', inviteCode: newInviteCode });
              });
          } else {
            return res.status(500).json({ error: '生成邀请码失败' });
          }
        } else {
          res.json({ message: '邀请码生成成功', inviteCode: inviteCode });
        }
      });
    });
  });
// });

// 获取用户的邀请码列表
app.get('/api/invite/list', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  
  db.all(`SELECT ic.*, u.username as used_by_username 
           FROM invite_codes ic 
           LEFT JOIN users u ON ic.used_by = u.id 
           WHERE ic.created_by = ? 
           ORDER BY ic.created_at DESC`, 
    [userId], (err, inviteCodes) => {
      if (err) {
        return res.status(500).json({ error: '获取邀请码列表失败' });
      }
      
      res.json({ inviteCodes });
    });
});

// 笔记相关API

// 获取用户的所有分组 - 必须放在 /api/notes 之前，避免路由冲突
app.get('/api/notes/groups', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  
  db.all(`SELECT ng.*, COUNT(n.id) as note_count 
           FROM note_groups ng 
           LEFT JOIN notes n ON ng.id = n.group_id 
           WHERE ng.user_id = ? 
           GROUP BY ng.id 
           ORDER BY ng.created_at DESC`, 
    [userId], (err, groups) => {
      if (err) {
        return res.status(500).json({ error: '获取分组失败' });
      }
      
      res.json({ groups });
    });
});

// 创建新分组
app.post('/api/notes/groups', authenticateToken, (req, res) => {
  const { name } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '分组名称不能为空' });
  }
  
  db.run('INSERT INTO note_groups (user_id, name, created_at) VALUES (?, ?, ?)', 
    [userId, name.trim(), currentTime], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '创建分组失败' });
      }
      res.json({ message: '分组创建成功', groupId: this.lastID });
    });
});

// 更新分组
app.put('/api/notes/groups/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { name } = req.body;
  const userId = req.user.userId;
  
  // 验证ID是否为数字
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的分组ID' });
  }
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '分组名称不能为空' });
  }
  
  db.run('UPDATE note_groups SET name = ? WHERE id = ? AND user_id = ?', 
    [name.trim(), id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '更新分组失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '分组不存在或无权限修改' });
      }
      res.json({ message: '分组更新成功' });
    });
});

// 删除分组
app.delete('/api/notes/groups/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  // 验证ID是否为数字
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的分组ID' });
  }
  
  // 开始事务
  db.serialize(() => {
    db.run('BEGIN TRANSACTION');
    
    // 删除该分组下的所有笔记
    db.run('DELETE FROM notes WHERE group_id = ? AND user_id = ?', 
      [id, userId], 
      function(err) {
        if (err) {
          db.run('ROLLBACK');
          return res.status(500).json({ error: '删除分组失败' });
        }
        
        // 删除分组
        db.run('DELETE FROM note_groups WHERE id = ? AND user_id = ?', 
          [id, userId], 
          function(err) {
            if (err) {
              db.run('ROLLBACK');
              return res.status(500).json({ error: '删除分组失败' });
            }
            if (this.changes === 0) {
              db.run('ROLLBACK');
              return res.status(404).json({ error: '分组不存在或无权限删除' });
            }
            
            db.run('COMMIT');
            res.json({ message: '分组删除成功' });
          });
      });
  });
});

// 获取用户的所有笔记（支持分页和搜索）
app.get('/api/notes', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  const { groupId, page = 1, limit = 10, keyword } = req.query;
  
  // 验证分页参数
  const pageNum = parseInt(page, 10);
  const limitNum = parseInt(limit, 10);
  
  if (isNaN(pageNum) || pageNum < 1 || isNaN(limitNum) || limitNum < 1) {
    return res.status(400).json({ error: '无效的分页参数' });
  }
  
  const offset = (pageNum - 1) * limitNum;
  
  // 查询笔记总数
  let countQuery = `SELECT COUNT(*) as total 
                   FROM notes n 
                   WHERE n.user_id = ?`;
  const countParams = [userId];
  
  if (groupId && /^\d+$/.test(groupId)) {
    countQuery += ' AND n.group_id = ?';
    countParams.push(parseInt(groupId, 10));
  }
  
  // 添加搜索关键词条件
  if (keyword && keyword.trim()) {
    countQuery += ' AND n.content LIKE ?';
    countParams.push('%' + keyword.trim() + '%');
  }
  
  // 查询笔记数据
  let query = `SELECT n.*, ng.name as group_name 
               FROM notes n 
               LEFT JOIN note_groups ng ON n.group_id = ng.id 
               WHERE n.user_id = ?`;
  const params = [userId];
  
  if (groupId && /^\d+$/.test(groupId)) {
    query += ' AND n.group_id = ?';
    params.push(parseInt(groupId, 10));
  }
  
  // 添加搜索关键词条件
  if (keyword && keyword.trim()) {
    query += ' AND n.content LIKE ?';
    params.push('%' + keyword.trim() + '%');
  }
  
  query += ' ORDER BY n.updated_at DESC';
  query += ' LIMIT ? OFFSET ?';
  params.push(limitNum, offset);
  
  // 先查询总数，再查询数据
  db.get(countQuery, countParams, (err, countResult) => {
    if (err) {
      console.error('Failed to count notes:', err.message);
      return res.status(500).json({ error: '获取笔记总数失败' });
    }
    
    const total = countResult.total;
    const totalPages = Math.ceil(total / limitNum);
    const hasMore = pageNum < totalPages;
    
    db.all(query, params, (err, notes) => {
      if (err) {
        console.error('Failed to fetch notes:', err.message, 'SQL:', query, 'Params:', params);
        return res.status(500).json({ 
          error: '获取笔记失败' 
        });
      }
      
      // 根据页码判断是否需要延迟
      if (pageNum === 1) {
        // 如果是第一页，立即响应
        res.json({ 
          notes: notes || [], 
          pagination: {
            page: pageNum,
            limit: limitNum,
            total: total,
            totalPages: totalPages,
            hasMore: hasMore
          }
        });
      } else {
        // 如果是其他页，延迟响应
        setTimeout(() => {
          res.json({ 
            notes: notes || [], 
            pagination: {
              page: pageNum,
              limit: limitNum,
              total: total,
              totalPages: totalPages,
              hasMore: hasMore
            }
          });
        }, 600);
      }
    });
  });
});

// 获取单个笔记
app.get('/api/notes/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  // 验证ID是否为数字
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的笔记ID' });
  }
  
  db.get(`SELECT n.*, ng.name as group_name 
           FROM notes n 
           LEFT JOIN note_groups ng ON n.group_id = ng.id 
           WHERE n.id = ? AND n.user_id = ?`, 
    [id, userId], (err, note) => {
      if (err) {
        return res.status(500).json({ error: '获取笔记失败' });
      }
      
      if (!note) {
        return res.status(404).json({ error: '笔记不存在' });
      }
      
      res.json({ note });
    });
});

// 创建新笔记
app.post('/api/notes', authenticateToken, (req, res) => {
  const { content, group_id } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!content || !content.trim()) {
    return res.status(400).json({ error: '笔记内容不能为空' });
  }
  
  db.run('INSERT INTO notes (user_id, group_id, content, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', 
    [userId, group_id || null, content.trim(), currentTime, currentTime], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '创建笔记失败' });
      }
      res.json({ 
        message: '笔记创建成功', 
        noteId: this.lastID,
        note: {
          id: this.lastID,
          content: content.trim(),
          group_id: group_id || null,
          created_at: currentTime,
          updated_at: currentTime
        }
      });
    });
});

// 更新笔记
app.put('/api/notes/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { content, group_id } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  // 验证ID是否为数字
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的笔记ID' });
  }
  
  if (!content || !content.trim()) {
    return res.status(400).json({ error: '笔记内容不能为空' });
  }
  
  db.run('UPDATE notes SET content = ?, group_id = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
    [content.trim(), group_id || null, currentTime, id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '更新笔记失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '笔记不存在或无权限修改' });
      }
      res.json({ message: '笔记更新成功' });
    });
});

// 删除笔记
app.delete('/api/notes/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  db.run('DELETE FROM notes WHERE id = ? AND user_id = ?', 
    [id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '删除笔记失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '笔记不存在或无权限删除' });
      }
      res.json({ message: '笔记删除成功' });
    });
});

// 获取用户的工作预设列表
app.get('/api/work-presets', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  
  db.all('SELECT * FROM work_presets WHERE user_id = ? ORDER BY created_at DESC', [userId], (err, presets) => {
    if (err) {
      return res.status(500).json({ error: '获取预设失败' });
    }
    res.json({ presets });
  });
});

// 创建新的工作预设
app.post('/api/work-presets', authenticateToken, (req, res) => {
  const { name, morning_start, morning_end, afternoon_start, afternoon_end, evening_start, evening_end } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '预设名称不能为空' });
  }
  
  if (!morning_start || !morning_end || !afternoon_start || !afternoon_end || !evening_start || !evening_end) {
    return res.status(400).json({ error: '请完整设置所有时间段' });
  }
  
  // 检查预设名称是否已存在
  db.get('SELECT id FROM work_presets WHERE user_id = ? AND name = ?', [userId, name.trim()], (err, existing) => {
    if (err) {
      return res.status(500).json({ error: '创建预设失败' });
    }
    
    if (existing) {
      return res.status(400).json({ error: '预设名称已存在，请使用其他名称' });
    }
    
    db.run('INSERT INTO work_presets (user_id, name, morning_start, morning_end, afternoon_start, afternoon_end, evening_start, evening_end, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', 
      [userId, name.trim(), morning_start, morning_end, afternoon_start, afternoon_end, evening_start, evening_end, currentTime, currentTime], 
      function(err) {
        if (err) {
          return res.status(500).json({ error: '创建预设失败' });
        }
        res.json({ 
          message: '预设创建成功', 
          presetId: this.lastID,
          preset: {
            id: this.lastID,
            name: name.trim(),
            morning_start,
            morning_end,
            afternoon_start,
            afternoon_end,
            evening_start,
            evening_end,
            created_at: currentTime,
            updated_at: currentTime
          }
        });
      });
  });
});

// 更新工作预设
app.put('/api/work-presets/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { name, morning_start, morning_end, afternoon_start, afternoon_end, evening_start, evening_end } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的预设ID' });
  }
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '预设名称不能为空' });
  }
  
  if (!morning_start || !morning_end || !afternoon_start || !afternoon_end || !evening_start || !evening_end) {
    return res.status(400).json({ error: '请完整设置所有时间段' });
  }
  
  // 检查预设名称是否已存在（排除当前预设）
  db.get('SELECT id FROM work_presets WHERE user_id = ? AND name = ? AND id != ?', [userId, name.trim(), id], (err, existing) => {
    if (err) {
      return res.status(500).json({ error: '更新预设失败' });
    }
    
    if (existing) {
      return res.status(400).json({ error: '预设名称已存在，请使用其他名称' });
    }
    
    db.run('UPDATE work_presets SET name = ?, morning_start = ?, morning_end = ?, afternoon_start = ?, afternoon_end = ?, evening_start = ?, evening_end = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
      [name.trim(), morning_start, morning_end, afternoon_start, afternoon_end, evening_start, evening_end, currentTime, id, userId], 
      function(err) {
        if (err) {
          return res.status(500).json({ error: '更新预设失败' });
        }
        if (this.changes === 0) {
          return res.status(404).json({ error: '预设不存在或无权限修改' });
        }
        res.json({ message: '预设更新成功' });
      });
  });
});

// 删除工作预设
app.delete('/api/work-presets/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的预设ID' });
  }
  
  db.run('DELETE FROM work_presets WHERE id = ? AND user_id = ?', 
    [id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '删除预设失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '预设不存在或无权限删除' });
      }
      res.json({ message: '预设删除成功' });
    });
});

// ==================== 倒数日模块API接口 ====================

// 获取所有分类（包含事件数量统计）
app.get('/api/categories', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  
  db.all(`SELECT c.*, COUNT(e.id) as event_count 
           FROM countdown_categories c 
           LEFT JOIN countdown_events e ON c.id = e.category_id AND e.user_id = ? 
           WHERE c.user_id = ? 
           GROUP BY c.id 
           ORDER BY c.sort_order ASC`, 
    [userId, userId], (err, categories) => {
      if (err) {
        return res.status(500).json({ error: '获取分类失败' });
      }
      res.json({ categories });
    });
});

// 创建新分类
app.post('/api/categories', authenticateToken, (req, res) => {
  const { name } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '分类名称不能为空' });
  }
  
  // 获取当前最大sort_order值
  db.get('SELECT MAX(sort_order) as maxOrder FROM countdown_categories WHERE user_id = ?', [userId], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '创建分类失败' });
    }
    
    const newOrder = (result.maxOrder || 0) + 1;
    
    db.run('INSERT INTO countdown_categories (user_id, name, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', 
      [userId, name.trim(), newOrder, currentTime, currentTime], 
      function(err) {
        if (err) {
          return res.status(500).json({ error: '创建分类失败' });
        }
        res.json({ 
          message: '分类创建成功', 
          categoryId: this.lastID,
          category: {
            id: this.lastID,
            name: name.trim(),
            order: newOrder,
            created_at: currentTime,
            updated_at: currentTime
          }
        });
      });
  });
});

// 批量更新分类排序
app.put('/api/categories/reorder', authenticateToken, (req, res) => {
  const { categories } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!Array.isArray(categories)) {
    return res.status(400).json({ error: '分类数据格式错误' });
  }
  
  // 验证所有分类数据格式
  for (const category of categories) {
    if (!category.id || category.order === undefined) {
      return res.status(400).json({ error: '分类数据格式错误' });
    }
    
    // 验证分类ID是否为有效数字
    if (!/^\d+$/.test(category.id.toString())) {
      return res.status(400).json({ error: '无效的分类ID' });
    }
  }
  
  // 开始事务
  db.serialize(() => {
    db.run('BEGIN TRANSACTION');
    
    let updateCount = 0;
    const totalCount = categories.length;
    let hasError = false;
    let responseSent = false;
    
    categories.forEach((category, index) => {
      db.run('UPDATE countdown_categories SET sort_order = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
        [category.order, currentTime, category.id, userId], 
        function(err) {
          if (err) {
            if (!responseSent) {
              responseSent = true;
              hasError = true;
              db.run('ROLLBACK');
              return res.status(500).json({ error: '更新分类顺序失败' });
            }
            return;
          }
          
          updateCount++;
          
          // 所有更新完成后提交事务
          if (updateCount === totalCount && !hasError && !responseSent) {
            responseSent = true;
            db.run('COMMIT');
            res.json({ message: '分类排序更新成功' });
          }
        });
    });
  });
});

// 更新分类名称
app.put('/api/categories/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { name } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  // if (!/^\d+$/.test(id)) {
  //   return res.status(400).json({ error: '无效的分类ID' });
  // }
  
  if (!name || !name.trim()) {
    return res.status(400).json({ error: '分类名称不能为空' });
  }
  
  db.run('UPDATE countdown_categories SET name = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
    [name.trim(), currentTime, id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '更新分类失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '分类不存在或无权限修改' });
      }
      res.json({ message: '分类更新成功' });
    });
});

// 删除分类
app.delete('/api/categories/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的分类ID' });
  }
  
  // 开始事务
  db.serialize(() => {
    db.run('BEGIN TRANSACTION');
    
    // 先检查分类是否存在
    db.get('SELECT id, name FROM countdown_categories WHERE id = ? AND user_id = ?', [id, userId], (err, category) => {
      if (err) {
        db.run('ROLLBACK');
        return res.status(500).json({ error: '删除分类失败' });
      }
      
      if (!category) {
        db.run('ROLLBACK');
        return res.status(404).json({ error: '分类不存在或无权限删除' });
      }
      
      // 阻止删除默认分类"未分组"
      if (category.name === '未分组') {
        db.run('ROLLBACK');
        return res.status(403).json({ error: '默认分类不能删除' });
      }
      
      // 查询用户的默认分类"未分组"
      db.get('SELECT id FROM countdown_categories WHERE user_id = ? AND name = ?', [userId, '未分组'], (err, defaultCategory) => {
        if (err) {
          db.run('ROLLBACK');
          return res.status(500).json({ error: '删除分类失败' });
        }
        
        // 将该分类下的事件移动到默认分类
        db.run('UPDATE countdown_events SET category_id = ? WHERE category_id = ? AND user_id = ?', 
          [defaultCategory.id, id, userId], 
          function(err) {
            if (err) {
              db.run('ROLLBACK');
              console.log("UPDATE:"+err.message);
              return res.status(500).json({ error: '删除分类失败' });
            }
            
            // 删除分类
            db.run('DELETE FROM countdown_categories WHERE id = ? AND user_id = ?', 
              [id, userId], 
              function(err) {
                if (err) {
                  db.run('ROLLBACK');
                  console.log("DELETE:"+err.message);
                  return res.status(500).json({ error: '删除分类失败' });
                }
                
                db.run('COMMIT');
                res.json({ message: '分类删除成功，该分类下的事件已移动到未分类' });
              }
            ); // 闭合DELETE语句的db.run
          }
        ); // 闭合UPDATE语句的db.run
      }); // 闭合SELECT语句的db.get
    }); // 闭合检查分类存在的db.get
  }); // 闭合事务的serialize
});

// 获取所有事件（支持分页）
app.get('/api/events', authenticateToken, (req, res) => {
  const userId = req.user.userId;
  const { categoryId, page = 1, limit = 10 } = req.query;
  
  // 验证分页参数
  const pageNum = parseInt(page, 10);
  const limitNum = parseInt(limit, 10);
  
  if (isNaN(pageNum) || pageNum < 1 || isNaN(limitNum) || limitNum < 1) {
    return res.status(400).json({ error: '无效的分页参数' });
  }
  
  const offset = (pageNum - 1) * limitNum;
  
  // 查询事件总数
  let countQuery = `SELECT COUNT(*) as total 
                   FROM countdown_events e 
                   WHERE e.user_id = ?`;
  const countParams = [userId];
  
  if (categoryId && /^\d+$/.test(categoryId)) {
    countQuery += ' AND e.category_id = ?';
    countParams.push(parseInt(categoryId, 10));
  }
  
  // 查询事件数据
  let query = `SELECT e.*, c.name as category_name 
               FROM countdown_events e 
               LEFT JOIN countdown_categories c ON e.category_id = c.id 
               WHERE e.user_id = ?`;
  const params = [userId];
  
  if (categoryId && /^\d+$/.test(categoryId)) {
    query += ' AND e.category_id = ?';
    params.push(parseInt(categoryId, 10));
  }
  
  query += ' ORDER BY e.is_pinned DESC, e.sort_order ASC';
  query += ' LIMIT ? OFFSET ?';
  params.push(limitNum, offset);
  
  // 先查询总数，再查询数据
  db.get(countQuery, countParams, (err, countResult) => {
    if (err) {
      console.error('Failed to count events:', err.message);
      return res.status(500).json({ error: '获取事件总数失败' });
    }
    
    const total = countResult.total;
    const totalPages = Math.ceil(total / limitNum);
    const hasMore = pageNum < totalPages;
    
    db.all(query, params, (err, events) => {
      if (err) {
        console.error('Failed to fetch events:', err.message, 'SQL:', query, 'Params:', params);
        return res.status(500).json({ 
          error: '获取事件失败' 
        });
      }
      
       // 根据页码判断是否需要延迟
  if (pageNum === 1) {
    // 如果是第一页，立即响应
    res.json({ 
      events: events || [], 
      pagination: {
        page: pageNum,
        limit: limitNum,
        total: total,
        totalPages: totalPages,
        hasMore: hasMore
      }
    });
  } else {
    // 如果是其他页，延迟 1 秒响应
    setTimeout(() => {
      res.json({ 
        events: events || [], 
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          totalPages: totalPages,
          hasMore: hasMore
        }
      });
    }, 600); // 延迟 1000 毫秒，即 1 秒
  }
    });
  });
});

// 获取单个事件详情
app.get('/api/events/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的事件ID' });
  }
  
  db.get(`SELECT e.*, c.name as category_name 
           FROM countdown_events e 
           JOIN countdown_categories c ON e.category_id = c.id 
           WHERE e.id = ? AND e.user_id = ?`, 
    [id, userId], (err, event) => {
      if (err) {
        return res.status(500).json({ error: '获取事件失败' });
      }
      
      if (!event) {
        return res.status(404).json({ error: '事件不存在' });
      }
      
      res.json({ event });
    });
});

// 创建新事件
app.post('/api/events', authenticateToken, (req, res) => {
  const { category_id, title, target_date, is_pinned } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  // 允许category_id为null（未分类）
  if (category_id !== null && (!/^\d+$/.test(category_id))) {
    return res.status(400).json({ error: '无效的分类ID' });
  }
  
  if (!title || !title.trim()) {
    return res.status(400).json({ error: '事件标题不能为空' });
  }
  
  if (!target_date) {
    return res.status(400).json({ error: '目标日期不能为空' });
  }
  
  // 如果指定了分类，验证分类是否存在且属于当前用户
  if (category_id !== null) {
    db.get('SELECT id FROM countdown_categories WHERE id = ? AND user_id = ?', [category_id, userId], (err, category) => {
      if (err) {
        return res.status(500).json({ error: '创建事件失败' });
      }
      
      if (!category) {
        return res.status(404).json({ error: '分类不存在或无权限' });
      }
      
      createEventWithCategory();
    });
  } else {
    createEventWithCategory();
  }
  
  function createEventWithCategory() {
    // 获取当前分类下的最大sort_order值
    const categoryFilter = category_id !== null ? 'category_id = ?' : 'category_id IS NULL';
    const queryParams = category_id !== null ? [category_id, userId] : [userId];
    
    db.get(`SELECT MAX(sort_order) as maxOrder FROM countdown_events WHERE ${categoryFilter} AND user_id = ?`, 
      queryParams, (err, result) => {
        if (err) {
          return res.status(500).json({ error: '创建事件失败' });
        }
        
        const newOrder = (result.maxOrder || 0) + 1;
        
        db.run('INSERT INTO countdown_events (user_id, category_id, title, target_date, is_pinned, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', 
          [userId, category_id, title.trim(), target_date, is_pinned ? 1 : 0, newOrder, currentTime, currentTime], 
          function(err) {
            if (err) {
              return res.status(500).json({ error: '创建事件失败' });
            }
            res.json({ 
              message: '事件创建成功', 
              eventId: this.lastID,
              event: {
                id: this.lastID,
                category_id: category_id ? parseInt(category_id) : null,
                title: title.trim(),
                target_date,
                is_pinned: is_pinned ? 1 : 0,
                sort_order: newOrder,
                created_at: currentTime,
                updated_at: currentTime
              }
            });
          });
      });
  }
});

app.put('/api/events/reorder', authenticateToken, (req, res) => {
  const { events } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!Array.isArray(events)) {
    return res.status(400).json({ error: '事件数据格式错误' });
  }

  // 提取并验证所有事件ID
  const eventIds = [];
  for (const event of events) {
    if (!event.id || event.order === undefined) {
      return res.status(400).json({ error: '事件数据格式错误' });
    }
    const id = parseInt(event.id, 10);
    if (isNaN(id)) {
      return res.status(400).json({ error: '无效的事件ID' });
    }
    eventIds.push(id);
  }

  // 处理SQLite的IN查询限制：手动拼接占位符
  const placeholders = eventIds.map(() => '?').join(',');
  const query = `SELECT id FROM countdown_events WHERE id IN (${placeholders}) AND user_id = ?`;
  const params = [...eventIds, userId]; // 合并事件ID和用户ID

  db.all(query, params, (err, validEvents) => {
    if (err) {
      return res.status(500).json({ error: '验证事件失败' });
    }
    const validIds = validEvents.map(e => e.id);
    const invalidEvent = events.find(event => {
      const id = parseInt(event.id, 10);
      return !validIds.includes(id);
    });
    if (invalidEvent) {
      return res.status(400).json({ error: '无效的事件ID（越权或不存在）' });
    }

    // 事务逻辑保持不变
    db.serialize(async () => {
      try {
        await db.run('BEGIN TRANSACTION');
        for (const event of events) {
          const id = parseInt(event.id, 10);
          await new Promise((resolve, reject) => {
            db.run(
              'UPDATE countdown_events SET sort_order = ?, updated_at = ? WHERE id = ? AND user_id = ?',
              [event.order, currentTime, id, userId],
              (err) => err ? reject(err) : resolve()
            );
          });
        }
        await db.run('COMMIT');
        res.json({ message: '事件排序更新成功' });
      } catch (err) {
        await db.run('ROLLBACK');
        res.status(500).json({ error: '保存顺序失败' });
      }
    });
  });
});

// 更新事件
app.put('/api/events/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { category_id, title, target_date, is_pinned } = req.body;
  const userId = req.user.userId;
  const currentTime = getBeijingTime();
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的事件ID' });
  }
  
  // 支持category_id为null（未分类事件）
  if (category_id !== null && category_id !== undefined && !/^\d+$/.test(category_id)) {
    return res.status(400).json({ error: '无效的分类ID' });
  }
  
  if (!title || !title.trim()) {
    return res.status(400).json({ error: '事件标题不能为空' });
  }
  
  if (!target_date) {
    return res.status(400).json({ error: '目标日期不能为空' });
  }
  
  // 如果category_id为null，直接更新事件
  if (category_id === null || category_id === undefined) {
    db.run('UPDATE countdown_events SET category_id = NULL, title = ?, target_date = ?, is_pinned = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
      [title.trim(), target_date, is_pinned ? 1 : 0, currentTime, id, userId], 
      function(err) {
        if (err) {
          return res.status(500).json({ error: '更新事件失败' });
        }
        if (this.changes === 0) {
          return res.status(404).json({ error: '事件不存在或无权限修改' });
        }
        res.json({ message: '事件更新成功' });
      });
  } else {
    // 验证分类是否存在且属于当前用户
    db.get('SELECT id FROM countdown_categories WHERE id = ? AND user_id = ?', [category_id, userId], (err, category) => {
      if (err) {
        return res.status(500).json({ error: '更新事件失败' });
      }
      
      if (!category) {
        return res.status(404).json({ error: '分类不存在或无权限' });
      }
      
      db.run('UPDATE countdown_events SET category_id = ?, title = ?, target_date = ?, is_pinned = ?, updated_at = ? WHERE id = ? AND user_id = ?', 
        [category_id, title.trim(), target_date, is_pinned ? 1 : 0, currentTime, id, userId], 
        function(err) {
          if (err) {
            return res.status(500).json({ error: '更新事件失败' });
          }
          if (this.changes === 0) {
            return res.status(404).json({ error: '事件不存在或无权限修改' });
          }
          res.json({ message: '事件更新成功' });
        });
    });
  }
});

// 删除事件
app.delete('/api/events/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const userId = req.user.userId;
  
  if (!/^\d+$/.test(id)) {
    return res.status(400).json({ error: '无效的事件ID' });
  }
  
  db.run('DELETE FROM countdown_events WHERE id = ? AND user_id = ?', 
    [id, userId], 
    function(err) {
      if (err) {
        return res.status(500).json({ error: '删除事件失败' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: '事件不存在或无权限删除' });
      }
      res.json({ message: '事件删除成功' });
    });
});

//获取谚语
// app.get('/api/getSaying',  async (req, res) => {
//   try {
//     const { data } = await axios.get('https://api.nnxv.cn/api/yiyan.php');
//     res.send(`${data.content}  ——   ${data.from}`);
//   } catch (e) {
//     res.status(500).send(`获取失败：${e.message}`);
//   }
// });

app.get('/api/getSaying', authenticateToken ,async (req, res) => {
  try {
    const { data } = await axios.get('https://v1.hitokoto.cn');
    res.send(`${data.hitokoto}  ——   ${data.from}`);
  } catch (e) {
    res.status(500).send('获取失败');
  }
});

app.get('/api/getBaiduImage', async (req, res) => {
    // 配置参数
    const COOKIES = `BAIDUID=A21096E92798896F13DA81A4CA31C0EC:FG=1; BAIDUID_BFESS=A21096E92798896F13DA81A4CA31C0EC:FG=1; ZFY=x3GC0XuDnv5blT0z7IQeDs0cwYYQiMIhP0CH8S9cwtE:C; PSTM=1760879007; BDRCVFR[4Sa5I932hZT]=-_EV5wtlMr0mh-8uz4WUvY; H_PS_PSSID=60279_63140_64004_64981_65243_65455_65502_65619_65600_65646_65663_65676_65671_65689_65702_65713_65755_65728_65759_65788_65780_65841; BIDUPSID=E040B5031490E98A7050428E01C80B8E; BA_HECTOR=8l812ka484a48l2g8ha40l2lal0h801kf9od225; H_WISE_SIDS_BFESS=63144_63327_63948_64650_64745_64812_64887_64840_64909_64924_64965_64972_65017_65087_65115_65140_65142_65138_65189_65204_65168_65230_65251_65144_65258_65271_65322; BDORZ=FAE1F8CFA4E8841CC28A015FEAEE495D; ppfuid=FOCoIC3q5fKa8fgJnwzbE67EJ49BGJeplOzf+4l4EOvDuu2RXBRv6R3A1AZMa49I27C0gDDLrJyxcIIeAeEhD8JYsoLTpBiaCXhLqvzbzmvy3SeAW17tKgNq/Xx+RgOdb8TWCFe62MVrDTY6lMf2GrfqL8c87KLF2qFER3obJGmVXQmqM6seEAgB/LlOs0+zGEimjy3MrXEpSuItnI4KDwxAMUTOS16BfoXMcd2lpC/gwNSQKKIDdXA6eDfuiw2FSzTrlj5E/uOapVz0bsq88JfUBiPDWCIHzuT3ouhyzdvGgLbz7OSojK1zRbqBESR5Pdk2R9IA3lxxOVzA+Iw1TWLSgWjlFVG9Xmh1+20oPSbrzvDjYtVPmZ+9/6evcXmhcO1Y58MgLozKnaQIaLfWRHYJbniad6MOTaDR3XV1dTJczMhRIrj2EkREqmFx7vDmTMWjBijJagct9+p4xbYn+neTNkbS2el0J2+pbyoXJb1Cqpo5DSt5/s+VU2JwSorDqfhwWryYacZfOfIdenDLSkJsc4rBzsbBPyjKAzWGBO7nCxNtgYtDo26K+8ukl31Y+/geIrmTvn+xVA1gAbbf1lkKhylX1zGsOVlJip30kecMEGvjdNWpsel/qfsfe5JBpqDTksMVoBr7nszRboiUHbedcq1mi/UXvX2b3lxbCLv4Mxoy+dFS3Fr9jSAmssiPARPZutqXQT8krr+KVakPUpdbkwv/8CHDu0C/Z5vtDeiYLQpEgFjmQoey69Fz+kM7Y5cg925MGCeBU4jWp2g2g+TANHdv6IPFy1kUa0K6y8Lf8R54gc6582UtCC9heC6ZqcZmAiWTa50VJEiAxy+4yUhpOuhw8bYyjFOBzzWtHbb/bZjYuTi397a21776RWy1hAfIX+LOYnlmJLoIfeQJGyV88cwcM1svp+zVDjUpc+tX+hKCutoGOSxoeD09wtPvIwnuV/iLBm4pHPyXZWSnWRx8VJ2MfThAR9JY85BUmPjqOlbhrblXFkBQG7n4JKiZyalk9X22FXe2MsdipOHFxSUHSqbPo2PPJzEor12m6lThR7YgmlfLt7kntJD5XeM7GEQw3OLo5dsSUeQDd6vDnkb72/TMYcbPW48WuSnSgMDL820G6v5sII3fbIl1IswRGEQw3OLo5dsSUeQDd6vDntVllC1+aCCc8K28RWpYmU0S24R9DDZVx3j3+tLLpw3BRuF/lI7yGQ5dEntCEMtnVaOBuNLhAhlddA7KpjMdW1UU5ZZ2QvNYYVj4McvsdmZl2ssb09Yk1KUaxhNd9iuw6w==; BDUSS=jBBWk5YdHk2UnpWfjR1dlVUbWVocTREWFkyYUZaTUZaM0o3SWFqZkFWZnpiaHhwSVFBQUFBJCQAAAAAAAAAAAEAAADxdORXc2FrbGZrYQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPPh9Gjz4fRoc; BDUSS_BFESS=jBBWk5YdHk2UnpWfjR1dlVUbWVocTREWFkyYUZaTUZaM0o3SWFqZkFWZnpiaHhwSVFBQUFBJCQAAAAAAAAAAAEAAADxdORXc2FrbGZrYQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPPh9Gjz4fRoc; H_WISE_SIDS=64981_65243_65455_65502_65619_65600_65646_65663_65689_65702_65713_65755_65728_65788_65780`;
    const BAIDU_SKIN_URL = 'https://www.baidu.com/home/skin/data/skin';
    const headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36',
        'Cookie': COOKIES
    };

    try {
        // 1. 请求百度皮肤接口数据
        const skinRes = await axios.get(BAIDU_SKIN_URL, { headers });
        const skinData = skinRes.data;

        // 2. 定位明星分类（index=4）
        const starCategory = skinData?.bsResult?.data?.[4]?.starData;
        if (!starCategory || starCategory.length === 0) {
            throw new Error('未获取到明星分类数据');
        }

        // 3. 随机选择明星子分类
        const randomStarIndex = Math.floor(Math.random() * starCategory.length);
        const starItem = starCategory[randomStarIndex];
        const imageList = starItem?.list;
        if (!imageList || imageList.length === 0) {
            throw new Error('明星分类下无图片数据');
        }

        // 4. 随机选择图片并生成URL
        const randomImgIndex = Math.floor(Math.random() * imageList.length);
        const dataIndex = imageList[randomImgIndex].dataindex;
        const imageUrl = `https://ss2.bdstatic.com/lfoZeXSm1A5BphGlnYG/skin/${dataIndex}.jpg`;

        // 5. 请求图片并返回给客户端
        const imgRes = await axios.get(imageUrl, { responseType: 'arraybuffer' });
        res.setHeader('Content-Type', 'image/jpeg');
        res.send(Buffer.from(imgRes.data));

    } catch (error) {
        // 错误处理
        res.status(500).json({
            code: 500,
            text: '解析失败',
            now: new Date().toLocaleString()
        });
    }
});

// 启动服务器
app.listen(PORT, () => {
  console.log(`服务器运行在端口 ${PORT}`);
});